USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorCommentsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorCommentsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorCommentsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)


	SELECT	CommentsAlphanumeric.CommentsAlphanumericId AS CommentId,
			Companies.CompanyId, 
			IndicatorTemplates.IndicatorId AS IndicatorTemplateId, 
			CommentsAlphanumeric.Number AS CommentNumber, 
			CommentsAlphanumeric.Text AS CommentText, 
			[dbo].fnGetCommentSources(CommentsAlphanumeric.CommentsAlphanumericId) AS Sources
	FROM	[dbo].Companies AS Companies 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = Companies.CompanyId
	INNER JOIN [dbo].IndicatorsInCompanies ON Companies.CompanyId = [dbo].IndicatorsInCompanies.CompanyId 
	INNER JOIN [dbo].Indicators AS Indicators ON [dbo].IndicatorsInCompanies.IndicatorId = Indicators.IndicatorId AND Companies.WorkingVersion - 1 = Indicators.Version
	INNER JOIN [dbo].Indicators AS IndicatorTemplates ON Indicators.Number = IndicatorTemplates.Number
	INNER JOIN [dbo].CommentsAlphanumeric AS CommentsAlphanumeric ON Indicators.IndicatorId = CommentsAlphanumeric.IndicatorId
	WHERE (Indicators.Enabled = 1) AND (IndicatorTemplates.Template = 1) 
	AND (Indicators.Template = 0) AND (Companies.WorkingVersion > 1002) 
	AND (Companies.IsParked = 0)
END
GO


